﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using COMExcel = Microsoft.OfficeInterop.Excel;

namespace UTLEXPORT
{
    public class UTLEXPORT
    {
        #region Private Filed
        private string _strFileName;
        private string _strFolderExport;
        COMExcel.Application exApp;
        COMExcel.Workbook exBook;
        COMExcel.Worksheet exSheet;

        #endregion
        public UTLEXPORT()
        {
            _strFileName = "";
            _strFolderExport = ConfigurationManager.AppSettings["PathExPortDefault"].ToString();
        }
        public UTLEXPORT(string strFileName)
        {

            _strFileName = strFileName;
            _strFolderExport = ConfigurationManager.AppSettings["PathExPortDefault"].ToString();
        }
        public string FileName
        {
            get { return _strFileName; }
            set { _strFileName = value; }
        }
        /// <summary>
        /// Lay noi dung tren datagrid, hoac datatable ket xuat ra file Excel
        /// </summary>
        ///<param name="strFileName">Ten file Excel Export ra</param>
        ///<param name="tbData">DataTable source can ket xuat ra Excel</param>        
        public void ExportExcel(string strFileName, DataTable tbData, string titile, string sheetname, string[] headername)
        {
            if (_strFileName.Equals(""))
            {
                _strFileName = UTLCOMMAND.UTLCommand.GetRootAppPath() + @"\" + _strFolderExport + @"\" + UTLCOMMAND.UTLCommand.CreateNameFileByDate();
            }
            OpenApp();
            WriteDatatoSheet(tbData, titile, sheetname, headername);
            SaveFile(strFileName);
            CloseApp();

        }
        /// <summary>
        /// Open COM application
        /// </summary>
        private void OpenApp()
        {
            //khoi dong chuong trinh excel
            exApp = new Microsoft.Office.Interop.Excel.Application();
            // tap file xls temp
            exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
            // lay sheet 1.
            exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
        }
        /// <summary>
        /// Close COM application
        /// </summary>
        private void CloseApp()
        {
            exBook.Close(false, false, false);
            exApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
        }
        /// <summary>
        /// Save file
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private bool SaveFile(string path)
        {
            try
            {
                // Ẩn chương trình
                exApp.Visible = false;
                // Save file
                exBook.SaveAs(path, COMExcel.XlFileFormat.xlWorkbookNormal,
                                null, null, false, false,
                                COMExcel.XlSaveAsAccessMode.xlExclusive,
                                false, false, false, false, false);
                CloseApp();
                return true;
            }
            catch (Exception)
            {
                CloseApp();
                return false;
            }
        }
        /// <summary>
        /// Write content to worksheet
        /// </summary>
        /// <param name="tblvalue"></param>
        /// <param name="titile"></param>
        /// <param name="sheetname"></param>
        /// <param name="headername"></param>
        private void WriteDatatoSheet(DataTable tblvalue, string titile, string sheetname, string[] headername)
        {
            int _row = 4;
            int _col = 2;
            exSheet.Activate();
            exSheet.Name = sheetname;
            //dinh nghia tieu de file
            COMExcel.Range _r = (COMExcel.Range)exSheet.Cells[3, 3];
            _r.Value2 = titile;
            //ghi du lieu ra
            for (int i = 0; i < headername.Length; i++)
            {
                _r = (COMExcel.Range)exSheet.Cells[_row, _col];
                _r.Columns.AutoFit();
                _r.Value2 = headername[i].ToString();
                _col += 1;
            }
            _col = 2;
            _row = 5;
            for (int i = 0; i < tblvalue.Rows.Count; i++)
            {
                for (int j = 0; j < tblvalue.Columns.Count; j++)
                {
                    _r = (COMExcel.Range)exSheet.Cells[_row, _col];
                    if (j == 0) _col = 2;
                    else
                        _col += 1;
                    _r.Value2 = tblvalue.Rows[i][j].ToString();
                    _r.Columns.AutoFit();
                }
                _row += 1;
            }
            //SaveFile("C:\\abc.xls");
        }

        /// <summary>
        /// Lay noi dung tren datagrid, hoac datatable ket xuat ra file Excel
        /// </summary>
        ///<param name="strFileName">Ten file Excel Export ra</param>
        ///<param name="tbData">DataTable source can ket xuat ra Excel</param>        
        public bool blExportExcel(string strFileName, DataTable tbData, string titile, string sheetname, string[] headername)
        {
            try
            {
                ExportExcel(strFileName, tbData, titile, sheetname, headername);
                return true;
            }
            catch (Exception)
            {                
                //Write log here
                return false;
            }
        }
        /// <summary>
        /// Lay noi dung tren datagrid, hoac datatable ket xuat ra file pdf
        /// </summary>
        ///<param name="strFileName">Ten file Excel Export ra</param>
        ///<param name="tbData">DataTable source can ket xuat ra Excel</param>        
        public void ExportPDF(string strFileName, DataTable tbData)
        {
            //viet code cho nay
        }
        /// <summary>
        /// Lay noi dung tren datagrid, hoac datatable ket xuat ra file pdf
        /// </summary>
        ///<param name="strFileName">Ten file Excel Export ra</param>
        ///<param name="tbData">DataTable source can ket xuat ra Excel</param>        
        public bool blExportPDF(string strFileName, DataTable tbData)
        {
            //viet code cho nay
            return true;
        }

    }

    public class UTLEXPORTEXCEL
    { 

    }
}
